package com.scuec.trainingmanagementsys.mapper;

import com.scuec.trainingmanagementsys.entity.DailyReport;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Mapper
@Repository
public interface DailyReportMapper {

    /**
     * 整表查询所有日报记录
     * @return 日报记录列表
     */
    @Select("SELECT DailyReportID, DailyReportContent, DrCreateTime, " +
            "TeamID, ProjectID, StudentID " +
            "FROM dailyreport")
    List<DailyReport> findAll();

    /**
     * 根据ID删除单条日报记录
     * @param dailyReportID 日报ID
     * @return 影响的行数
     */
    @Delete("DELETE FROM dailyreport WHERE DailyReportID = #{dailyReportID}")
    int deleteById(Integer dailyReportID);

    /**
     * 插入新的日报记录
     * @param dailyReport 日报实体
     * @return 影响的行数
     */
    @Insert("INSERT INTO dailyreport (" +
            "DailyReportContent, DrCreateTime, TeamID, " +
            "ProjectID, StudentID) " +
            "VALUES (" +
            "#{dailyReportContent}, #{drCreateTime}, #{teamID}, " +
            "#{projectID}, #{studentID})")
    @Options(useGeneratedKeys = true, keyProperty = "dailyReportID")
    int insert(DailyReport dailyReport);

    /**
     * 根据学生ID查询日报记录
     * @param studentID 学生ID
     * @return 日报记录列表
     */
    @Select("SELECT DailyReportID, DailyReportContent, DrCreateTime, " +
            "TeamID, ProjectID, StudentID " +
            "FROM dailyreport WHERE StudentID = #{studentID}")
    List<DailyReport> findByStudentID(Integer studentID);

    /**
     * 根据项目ID查询日报记录
     * @param projectID 项目ID
     * @return 日报记录列表
     */
    @Select("SELECT DailyReportID, DailyReportContent, DrCreateTime, " +
            "TeamID, ProjectID, StudentID " +
            "FROM dailyreport WHERE ProjectID = #{projectID}")
    List<DailyReport> findByProjectID(Integer projectID);

    /**
     * 查询所有日报记录，关联学生表和团队表
     * @return 包含学生姓名和团队名称的日报记录列表
     */
    @Select("SELECT dr.DailyReportID, dr.DailyReportContent, dr.DrCreateTime, " +
            "dr.TeamID, dr.ProjectID, dr.StudentID, " +
            "s.StudentName, t.teamName " +
            "FROM dailyreport dr " +
            "LEFT JOIN student s ON dr.StudentID = s.studentID " +
            "LEFT JOIN team t ON dr.TeamID = t.teamID")
    List<Map<String, Object>> findAllWithRelations();

    /**
     * 根据学生ID查询日报记录并关联学生表、项目表和团队表
     * @param studentID 学生ID
     * @return 包含学生姓名、项目名称和团队名称的日报记录列表
     */
    @Select("SELECT dr.DailyReportID, dr.DailyReportContent, dr.DrCreateTime, " +
            "dr.TeamID, dr.ProjectID, dr.StudentID, " +
            "s.StudentName, p.projectName, t.teamName " +
            "FROM dailyreport dr " +
            "LEFT JOIN student s ON dr.StudentID = s.studentID " +
            "LEFT JOIN project p ON dr.ProjectID = p.projectId " +
            "LEFT JOIN team t ON dr.TeamID = t.teamID " +
            "WHERE dr.StudentID = #{studentID}")
    List<Map<String, Object>> findByStudentIDWithRelations(Integer studentID);

    @Select("SELECT dr.DailyReportID, dr.DailyReportContent, dr.DrCreateTime, " +
            "dr.TeamID, dr.ProjectID, dr.StudentID, " +
            "s.StudentName, t.teamName, p.projectName " +
            "FROM dailyreport dr " +
            "LEFT JOIN student s ON dr.StudentID = s.studentID " +
            "LEFT JOIN team t ON dr.TeamID = t.teamID " +
            "LEFT JOIN project p ON dr.ProjectID = p.projectId " +
            "WHERE dr.ProjectID = #{projectID}")
    List<Map<String, Object>> findByProjectIDWithRelations(Integer projectID);

    @Select("SELECT DATE(DrCreateTime) as date, COUNT(*) as count " +
            "FROM dailyreport " +
            "WHERE DrCreateTime >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) " +
            "GROUP BY DATE(DrCreateTime) " +
            "ORDER BY DATE(DrCreateTime)")
    List<Map<String, Object>> selectDailyCountLast7Days();
}